package com.mc.dao.Impl;

import com.mc.VO.STVO;
import com.mc.bean.ST;
import com.mc.dao.STDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zl
 * 团队学生关系数据访问接口实现类
 */
public class STDaoImpl extends BaseDao implements STDao {

    @Override
    public List<STVO> findByTeamId(int teamid) {
        List<STVO> list = new ArrayList<>();
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select st.`teamid`,student.sid,student.sname,student.ssex,student.sage,student.stel from st,student where st.sid=student.sid and teamid = ?";
            super.getPst(sql);
            pstmt.setInt(1,teamid);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()){
                //创建对象
                STVO stvo = new STVO(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getString(6));
                //放在集合中
                list.add(stvo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return list;
    }

    @Override
    public List<ST> findBySid(int sid) {
        List<ST> list = new ArrayList<>();
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from st where sid = ?";
            super.getPst(sql);
            pstmt.setInt(1,sid);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()){
                //创建对象
                ST st = new ST(rs.getInt(1), rs.getInt(2));
                //放在集合中
                list.add(st);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return list;
    }

    @Override
    public int add(ST st) {
        int i = 0;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "insert into st(teamid,sid) value(?,?)";
            super.getPst(sql);
            pstmt.setInt(1,st.getTeamid());
            pstmt.setInt(2,st.getSid());
            //4执行预编译指令+得到结果集
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }

        return i;
    }

    @Override
    public int delete(int teamid, int sid) {
        int i = 0;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "delete from st where teamid = ? and sid = ?";
            super.getPst(sql);
            pstmt.setInt(1,teamid);
            pstmt.setInt(2,sid);
            //4执行预编译指令+得到结果集
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }

        return i;
    }

    @Override
    public int update(ST st) {
        int i = 0;
        return i;
    }
}
